#!/usr/bin/env python
# -*- coding: utf-8 -*-

__author__ = 'James Iter'
__date__ = '15/12/27'
__contact__ = 'james.iter.cn@gmail.com'
__copyright__ = '(c) 2015 by James Iter.'


import traceback
import jimit as ji
import mysql.connector
from mysql.connector import errorcode
import json
from random import choice
import sys

sys.path.append("..")
from models import Utils
from models.initialize import app, Init, logger, objects_model, object_db_map, db_conn_map, cold_db_conn_map, states, \
    cold_db_conn_map_list, cold_db_conn_map_list_reversed

this_cycle_begin_ts = Utils.get_the_cycle_begin_ts(cycle_unit=app.config['DUMP_CYCLE'], offset=0) * app.config['TIME_X']
last_cycle_begin_ts = Utils.get_the_cycle_begin_ts(cycle_unit=app.config['DUMP_CYCLE'], offset=1) * app.config['TIME_X']
cycle_date = int(Utils.ts_to_date(ji.Common.ts()) / 100)


def sql_stmt_fetchall(_cnx=None, _sql_stmt=None):
    if _cnx is None or _sql_stmt is None:
        raise ValueError('_cnx and _sql_stmt must not None')

    _cursor = _cnx.cursor(dictionary=True, buffered=False)
    try:
        _cursor.execute(_sql_stmt)
        return _cursor.fetchall()
    except mysql.connector.Error as err:
        ret = dict()
        ret['state'] = ji.Common.exchange_state(50050)
        ret['state']['sub']['zh-cn'] = ''.join([ret['state']['sub']['zh-cn'], ': ', err._full_msg])
        logger.error(_sql_stmt)
        logger.error(err)
        raise ji.PreviewingError(json.dumps(ret))
    finally:
        _cursor.close()
        _cnx.close()


def fetch_object_fields(_cnx=None, _object_name=None):
    if _cnx is None or _object_name is None:
        raise ValueError('_cnx and _object_name must not None')

    _sql_stmt = ''.join(['SELECT * FROM ', _object_name, ' LIMIT 0'])
    _cursor = _cnx.cursor(dictionary=True, buffered=False)
    try:
        _cursor.execute(sql_stmt)
        _columns = list()
        for desc in _cursor.description:
            _columns.append(desc[0])
        return _columns
    except mysql.connector.Error as err:
        ret = dict()
        ret['state'] = ji.Common.exchange_state(50050)
        ret['state']['sub']['zh-cn'] = ''.join([ret['state']['sub']['zh-cn'], ': ', err._full_msg])
        logger.error(_sql_stmt)
        logger.error(err)
        raise ji.PreviewingError(json.dumps(ret))
    finally:
        _cursor.fetchall()
        _cursor.close()
        _cnx.close()


def insert_rows(_cnx=None, _name=None, _rows=None):
    if _cnx is None or _name is None or _rows is None:
        raise ValueError('_cnx, _name and _rows must not None')

    if _rows.__len__() > 0:
        _columns = _rows[0].keys()
        _insert_keys = ', '.join(_columns)
        _insert_values = ')s, %('.join(_columns)
        # 转存数据插入语句
        _sql_stmt = (''.join(['INSERT INTO ', _name, '(', _insert_keys, ') VALUES (%(', _insert_values, ')s)']))
        _cursor = _cnx.cursor()
        try:
            for _row in _rows:
                _cursor.execute(_sql_stmt, _row)
            _cnx.commit()
        except mysql.connector.Error as err:
            ret = dict()
            ret['state'] = ji.Common.exchange_state(50050)
            ret['state']['sub']['zh-cn'] = ''.join([ret['state']['sub']['zh-cn'], ': ', err._full_msg])
            logger.error(_sql_stmt)
            logger.error(err)
            raise ji.PreviewingError(json.dumps(ret))
        finally:
            _cursor.close()
            _cnx.close()


if __name__ == '__main__':
    # noinspection PyBroadException
    try:
        Init.init_db_conn()
        Init.init_cold_db_conn()
        Init.init_object_db_map()
        Init.init_objects_model()
        # 取出当期最小和最大id
        # 最小id为当月冷库最后一个记录的id,如果为月初,则取热库中当期最小id
        # 最大id为当期中最后一个记录的id
        min_id = 0
        max_id = 0

        # 热库
        hot_db_s = db_conn_map[states.DBDomain.hot.value]['db_group_s'][-1]
        # 当期的冷库
        cold_db_s = None

        for cold_object_db in cold_db_conn_map_list:
            if cold_object_db['sequence'] == cycle_date:
                cold_db_s = cold_object_db['cnx_s']
                break

        if cold_db_s is None:
            logger.critical('cold_db_s must not None')
            print 'cold_db_s must not None'
            exit()

        # 获取适当链接
        cold_cnxpool = choice(cold_db_s)

        # 遍历热库中的名字
        for name, object_db in object_db_map.items():
            if object_db['domain'] != states.DBDomain.hot.value:
                continue

            # 判断当前冷库中是否存在将要转存的目标数据表
            sql_stmt = ''.join(['SHOW TABLES LIKE "', name, '"'])
            cnx = cold_cnxpool.get_connection()
            rows = sql_stmt_fetchall(_cnx=cnx, _sql_stmt=sql_stmt)
            if rows is None or rows.__len__() != 1:
                # TODO: 优化,当不存在,根据热库现有结构来创建
                msg = ''.join([name, 'not exist in ', str(cycle_date), ' got: ', str(rows)])
                logger.critical(msg)
                print msg
                exit()

            # 获取将转存数据表的字段结构
            cnx = cold_cnxpool.get_connection()

            # 尝试从冷库取当期最大id值(正常情况下,上一周期的最后一个id加1,即为本周期的第一个id.如果本周起中,该脚本执行中途退出,
            # 那么其最后一个id加1,将可以连续之前已经转存的数据,从而没必要再从头走一遍)
            # 如果本周期为本月第一个周期,那么最小id值从热库中获取
            # 由于between是两边包含,所以last_cycle_begin_ts-1避免跨周期

            min_sql_stmt = ''.join(['SELECT id FROM ', name, ' WHERE ', app.config['TIME_LINE_FIELD'], ' BETWEEN ',
                                    str(last_cycle_begin_ts), ' AND ', str(this_cycle_begin_ts - 1),
                                    ' ORDER BY id LIMIT 1'])
            max_sql_stmt = ''.join(['SELECT id FROM ', name, ' WHERE ', app.config['TIME_LINE_FIELD'], ' BETWEEN ',
                                    str(last_cycle_begin_ts), ' AND ', str(this_cycle_begin_ts - 1),
                                    ' ORDER BY id DESC LIMIT 1'])
            cnx = cold_cnxpool.get_connection()
            min_rows = sql_stmt_fetchall(_cnx=cnx, _sql_stmt=max_sql_stmt)
            min_got_flag = False
            if min_rows.__len__() > 0:
                min_got_flag = True

            max_rows = []

            if object_db['rw_mode'] == states.RWMode.BW_AR.value:
                # 取最后一组来聚合读
                for cnxpool in hot_db_s:
                    if not min_got_flag:
                        cnx = cnxpool.get_connection()
                        min_rows.extend(sql_stmt_fetchall(_cnx=cnx, _sql_stmt=min_sql_stmt))
                    cnx = cnxpool.get_connection()
                    max_rows.extend(sql_stmt_fetchall(_cnx=cnx, _sql_stmt=max_sql_stmt))

            elif object_db['rw_mode'] == states.RWMode.SW_SR.value:
                # 随机取最后一组中的一个来读
                cnxpool = choice(hot_db_s)
                if min_rows is None:
                    cnx = cnxpool.get_connection()
                    min_rows.extend(sql_stmt_fetchall(_cnx=cnx, _sql_stmt=min_sql_stmt))
                cnx = cnxpool.get_connection()
                max_rows = sql_stmt_fetchall(_cnx=cnx, _sql_stmt=max_sql_stmt)
            else:
                raise OSError('What?')

            min_rows.sort()
            max_rows.sort()

            if min_rows.__len__() > 0:
                min_id = min_rows[0]['id']

            if max_rows.__len__() > 0:
                max_id = max_rows[-1]['id']

            print ''.join(['MIN_ID: ', str(min_id)])
            print ''.join(['MAX_ID: ', str(max_id)])
            # 处理转存的逻辑
            step = 100
            dump_min_id = min_id
            dump_max_id = 0
            # 最后一次转存,dump_max_id必定等于max_id
            while dump_max_id < max_id:
                dump_max_id = dump_min_id + step
                if dump_max_id > max_id:
                    dump_max_id = max_id

                # 转存数据获取语句 默认以主键id顺序返回,这样可以在后面的排序中相对减少复杂度
                sql_stmt = ''.join(['SELECT * FROM ', name, ' WHERE ', str(dump_min_id),
                                    ' < id AND id <= ', str(dump_max_id)])
                dump_min_id = dump_max_id
                dump_rows = list()
                if object_db['rw_mode'] == states.RWMode.BW_AR.value:
                    # 取最后一组来聚合读
                    for cnxpool in hot_db_s:
                        cnx = cnxpool.get_connection()
                        dump_rows.extend(sql_stmt_fetchall(_cnx=cnx, _sql_stmt=sql_stmt))

                elif object_db['rw_mode'] == states.RWMode.SW_SR.value:
                    # 随机取最后一组中的一个来读
                    cnxpool = choice(hot_db_s)
                    cnx = cnxpool.get_connection()
                    dump_rows = sql_stmt_fetchall(_cnx=cnx, _sql_stmt=sql_stmt)
                else:
                    raise OSError('What?')

                if object_db['rw_mode'] == states.RWMode.BW_AR.value:
                    dump_rows.sort(key=lambda _item: _item['id'])

                # 转存数据插入语句
                cnx = cold_cnxpool.get_connection()
                insert_rows(_cnx=cnx, _name=name, _rows=dump_rows)

    except:
        logger.error(traceback.format_exc())
